﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SqlOrm.ScriptBuilder
{
    public class SQLCompact35ScriptBuilder: IScriptBuilder
    {
        public Xml.DataDictionary DDL { get; set; }

        public void UseDatabase(System.IO.StreamWriter sw, int d)
        {
            
        }

        public void DropConstraints(System.IO.StreamWriter sw, int d)
        {
            var def = DDL.Definition;
            foreach (var table in def.Schema.Table)
            {
                foreach (var f in table.Field)
                {
                    if (f.FK != null)
                    {
                        string fkname = string.Format("FK_{0}_{1}", table.ClassName, def.Schema.GetTable(f.FK).ClassName);
                        sw.WriteLine(Indent(d) + string.Format("/****** Object:  ForeignKey {0}    Script Date: {1} ******/", fkname, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                        //sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{0}') AND parent_object_id = OBJECT_ID(N'{1}'))", fkname, table.FullyQualifiedSqlName));
                        sw.WriteLine(Indent(d) + string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]", table.name, fkname));
                        sw.WriteLine(Indent(d) + string.Format("GO"));
                    }
                    if (!string.IsNullOrEmpty(f.EnumName))
                    {
                        string efkname = string.Format("FK_{0}_{1}", table.ClassName, f.EnumLookupTableName);
                        sw.WriteLine(Indent(d) + string.Format("/****** Object:  ForeignKey [{0}]    Script Date: {1} ******/", efkname, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                        //sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'{1}'))", efkname, table.FullyQualifiedSqlName));
                        sw.WriteLine(Indent(d) + string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]", table.name, efkname));
                        sw.WriteLine(Indent(d) + string.Format("GO"));
                    }
                }
            }
        }

        public void DropTables(System.IO.StreamWriter sw, int d)
        {
            foreach (var table in DDL.Definition.Schema.Table)
            {
                sw.WriteLine(Indent(d) + string.Format("/****** Object:  Table [dbo].[{0}]    Script Date: {1} ******/", table.name, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                //sw.WriteLine(Indent(d) + "IF EXISTS (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '{0}' and TABLE_NAME = '{1}')".F(table.schema, table.name));
                //sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'U'))", table.FullyQualifiedSqlName));
                sw.WriteLine(Indent(d) + string.Format("DROP TABLE [{0}]", table.name));
                sw.WriteLine(Indent(d) + string.Format("GO"));
            }
            foreach (var table in DDL.Definition.Schema.Table)
            {
                foreach (var f in table.Field.Where(x => !string.IsNullOrEmpty(x.EnumName)))
                {
                    sw.WriteLine(Indent(d) + string.Format("/****** Object:  Table [dbo].[{0}]    Script Date: {1} ******/", f.EnumLookupTableName, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                    //sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U'))", f.EnumLookupTableName));
                    sw.WriteLine(Indent(d) + string.Format("DROP TABLE [{0}]", f.EnumLookupTableName));
                    sw.WriteLine(Indent(d) + string.Format("GO"));
                }
            }
        }

        public void CreateTables(System.IO.StreamWriter sw, int d)
        {
            foreach (var table in DDL.Definition.Schema.Table)
            {
                sw.WriteLine(Indent(d) + string.Format("/****** Object:  Table [{0}]    Script Date: {1} ******/", table.name, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                sw.WriteLine(Indent(d++) + string.Format("CREATE TABLE [{0}](", table.name));
                sw.Write(table.Field.SepList(f => Indent(d) + "[{0}] {1}{2}{3}".F(f.name, f.SqlDeclaredType, (f.nulls) ? " NULL" : " NOT NULL", f.rowguidcol?" ROWGUIDCOL":""), f => ",\r\n"));
                var keys = table.Field.Where(x => x.key);
                if (keys.Count() > 0)
                {
                    sw.WriteLine(",");
                    sw.Write(Indent(d) + string.Format("CONSTRAINT [PK_{0}] PRIMARY KEY (", table.ClassName));
                    sw.WriteLine(keys.SepList(k => "[{0}]".F(k.name), k => ", ") + ")");
                }
                sw.WriteLine(Indent(--d) + string.Format(")"));
                sw.WriteLine(Indent(d) + string.Format("GO"));
            }
            // enum tables
            foreach (var table in DDL.Definition.Schema.Table)
            {
                foreach (var f in table.Field.Where(x => !string.IsNullOrEmpty(x.EnumName)))
                {
                    int maxTextSize = f.Enum.Max(x => x.desc.Length);
                    sw.WriteLine(Indent(d) + string.Format("/****** Object:  Table [{0}]    Script Date: {1} ******/", f.EnumLookupTableName, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                    sw.WriteLine(Indent(d++) + string.Format("CREATE TABLE [{0}](", f.EnumLookupTableName));
                    sw.WriteLine(Indent(d) + string.Format("[id] {0} NOT NULL PRIMARY KEY,", f.SqlDataType));
                    sw.WriteLine(Indent(d) + string.Format("[desc] nvarchar({0}) NOT NULL", maxTextSize.ToString()));
                    sw.WriteLine(Indent(--d) + string.Format(")"));
                    sw.WriteLine(Indent(d) + string.Format("GO"));

                    foreach (var e in f.Enum)
                    {
                        string enumVal = string.Empty;
                        if (!string.IsNullOrEmpty(e.value))
                            enumVal = e.value;
                        else
                            enumVal = (Array.IndexOf(f.Enum, e) + 1).ToString();
                        sw.WriteLine(Indent(d) + string.Format("insert into [{0}] ( [id], [desc] ) values ( {1}, '{2}' )", f.EnumLookupTableName, enumVal, e.desc));
                        sw.WriteLine(Indent(d) + "GO");
                    }
                }
            }
        }

        public void CreateIndeces(System.IO.StreamWriter sw, int d)
        {
            foreach (var table in DDL.Definition.Schema.Table.Where(x=>x.Indexes!=null && x.Indexes.Index!=null))
            {
                foreach (var i in table.Indexes.Index)
                {
                    sw.WriteLine(Indent(d) + string.Format("/****** Object:  Index [{0}]    Script Date: {1} ******/", i.name, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                    sw.WriteLine(Indent(d) + string.Format("CREATE{0} INDEX [{1}] on {2} ({3})"
                        ,(i.unique)?" UNIQUE":"" 
                        //, i.type == Xml.IndexType.clustered? " CLUSTERED": " NONCLUSTERED"
                        , i.name
                        , table.FullyQualifiedSqlName
                        , i.IndexField.SepList(f=>string.Format("[{0}]{1}", f.column, ((f.sort == Xml.SortOrder.desc) ? " desc":"")),f=>", ")));
                    sw.WriteLine(Indent(d) + "GO");
                }
            }
        }
        public void DropIndeces(System.IO.StreamWriter sw, int d)
        {
            foreach (var table in DDL.Definition.Schema.Table.Where(x => x.Indexes != null && x.Indexes.Index != null))
            {
                foreach (var i in table.Indexes.Index)
                {
                    sw.WriteLine(Indent(d) + string.Format("/****** Object:  Index [{0}]    Script Date: {1} ******/", i.name, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                    sw.WriteLine(Indent(d) + string.Format("DROP INDEX {0}.[{1}]",table.FullyQualifiedSqlName, i.name));
                    sw.WriteLine(Indent(d) + "GO");
                }
            }
        }

        public void CreateConstraints(System.IO.StreamWriter sw, int d)
        {
            var schema = DDL.Definition.Schema;
            foreach (var table in schema.Table)
            {
                foreach (var f in table.Field)
                {
                    if (f.FK != null)
                    {
                        var fkTable = schema.Table.Single(x => x.name == f.FK.table && ((x.schema == "dbo" && string.IsNullOrEmpty(f.FK.schema)) || x.schema == f.FK.schema));
                        var fkCol = fkTable.Field.Single(x => x.name == f.FK.column);
                        if (fkTable != null)
                        {
                            string fkname = string.Format("FK_{0}_{1}", table.ClassName, schema.GetTable(f.FK).ClassName);
                            sw.WriteLine(Indent(d) + string.Format("/****** Object:  ForeignKey {0}    Script Date: {1} ******/", fkname, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                            //sw.WriteLine(Indent(d) + string.Format("IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{0}') AND parent_object_id = OBJECT_ID(N'{1}'))", fkname, table.FullyQualifiedSqlName));
                            sw.WriteLine(Indent(d) + string.Format("ALTER TABLE [{0}] ADD  CONSTRAINT [{1}] FOREIGN KEY([{2}])", table.name, fkname, f.name));
                            sw.WriteLine(Indent(d) + string.Format("REFERENCES [{0}] ([{1}])", f.FK.table, f.FK.column));
                            sw.WriteLine(Indent(d) + string.Format("GO"));
                            //sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'{1}'))", fkname, table.FullyQualifiedSqlName));
                            //sw.WriteLine(Indent(d) + string.Format("ALTER TABLE [{0}] CHECK CONSTRAINT [{1}]", table.name, fkname));
                            //sw.WriteLine(Indent(d) + string.Format("GO"));
                        }
                    }
                    if (!string.IsNullOrEmpty(f.EnumName))
                    {
                        string efkname = string.Format("FK_{0}_{1}", table.ClassName, f.EnumLookupTableName);
                        sw.WriteLine(Indent(d) + string.Format("/****** Object:  ForeignKey [{0}]    Script Date: {1} ******/", efkname, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                        //sw.WriteLine(Indent(d) + string.Format("IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'{1}'))", efkname, table.FullyQualifiedSqlName));
                        sw.WriteLine(Indent(d) + string.Format("ALTER TABLE [{0}] ADD  CONSTRAINT [{1}] FOREIGN KEY([{2}])", table.name, efkname, f.name));
                        sw.WriteLine(Indent(d) + string.Format("REFERENCES [{0}] ([{1}])", f.EnumLookupTableName, "id"));
                        sw.WriteLine(Indent(d) + string.Format("GO"));
                        //sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'{1}'))", efkname, table.FullyQualifiedSqlName));
                        //sw.WriteLine(Indent(d) + string.Format("ALTER TABLE [{0}] CHECK CONSTRAINT [{1}]", table.name, efkname));
                        //sw.WriteLine(Indent(d) + string.Format("GO"));
                    }
                }
            }
        }

        public void DropProcs(System.IO.StreamWriter sw, int d)
        {

        }

        public void CreateUpsertProcs(System.IO.StreamWriter sw, int d)
        {
            
        }

        public void CreateInsertProcs(System.IO.StreamWriter sw, int d)
        {
            
        }

        public void CreateGetProcs(System.IO.StreamWriter sw, int d)
        {
            
        }

        public void CreateDeleteProcs(System.IO.StreamWriter sw, int d)
        {
            
        }

        public void CreateSearchProcs(System.IO.StreamWriter sw, int d)
        {
            
        }

        public void CreateAllProcs(System.IO.StreamWriter sw, int d)
        {
            
        }

        public void CreateCustomProcs(System.IO.StreamWriter sw, int d)
        {
            
        }

        public static string Indent(int num)
        {
            if (num == 0)
                return string.Empty;
            return new string('\t', num);
        }
    }
}
